import json
import time

from django.db import connection
from django.http import JsonResponse
from rest_framework.decorators import api_view, permission_classes
from rest_framework.permissions import IsAuthenticated

from apps.rb_data.get_data import process_show, show_monitor_list, modify_monitor_lsit, time_check
from apps.rb_data.get_fields import getsettlecode, getsettleowner, getordercode, getyear, getstat, getpage, \
    getsettlecode_cheat, getsettleowner_cheat, getordercode_cheat, getyear_cheat
from apps.utils.mysql_utils import DBUtil_Dict, DBUtil


@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def settle_query(request):
    request.params = json.loads(request.body)
    # if auth_log().log_check(request) is False:
    #     return JsonResponse({'ret': 302, 'msg': '未登录'}, status=302)
    data = request.params['data']
    # type = request.params['type']
    dbs = DBUtil_Dict()
    page_size = data.get('page_size')
    page_num = data.get('page_num')
    offset = (page_num-1) * page_size

    data['page_num'] = offset
    sql_query_data_cheat_scan_res, sql_total = query_order_repeate_sql_create('RB_Settle_Check', 'case_relationship', data)
    res = dbs.get_all(sql_query_data_cheat_scan_res)
    res_total = dbs.get_all(sql_total)
    global total_num
    total_num = 0
    if res_total:
        total_num = res_total[0].get("total")
    return JsonResponse({'ret': 0, "total": total_num, "data": res, 'msg': '查询成功！'}, safe=False)


def query_order_repeate_sql_create(table_name1, table_name2, data):
    dict_param = dict()
    for k, v in data.items():
        dict_param[k] = v

    # sql = "SELECT if(ordercode is null,'',ordercode) ordercode,if(settlecode is null,'',settlecode) settlecode,if(submitdate is null,'',submitdate) submitdate,if(reaptetimes is null,'',reaptetimes) reaptetimes,if(realrefund is null,'',realrefund) realrefund,if(shouldrefund is null,'',shouldrefund) shouldrefund,if(settleowner is null,'',settleowner) settleowner,if(casename is null,0,1) stat FROM %s a left join %s b on a.`ordercode`=cast(b.markkey as SIGNED)" % (table_name1, table_name2) + " where 1=1" + getsettlecode(dict_param) + \
    #       getsettleowner(dict_param) + getordercode(dict_param) + getyear(dict_param)
    sql_query_order_repeate_sql_create = "select * from (SELECT if(ordercode is null,'',ordercode) ordercode,if(settlecode is null,'',settlecode) settlecode,if(submitdate is null,'',submitdate) submitdate,if(reaptetimes is null,'',reaptetimes) reaptetimes,if(realrefund is null,'',realrefund) realrefund,if(shouldrefund is null,'',shouldrefund) shouldrefund,if(settleowner is null,'',settleowner) settleowner,if(casename is null,0,1) stat FROM %s a left join %s b on a.`ordercode`=cast(b.markkey as SIGNED)" % (
    table_name1, table_name2) + " where 1=1" + getsettlecode(dict_param) + \
          getsettleowner(dict_param) + getordercode(dict_param) + getyear(dict_param) + ") tmp where 1=1 " + getstat(dict_param) + getpage(dict_param)
    print(sql_query_order_repeate_sql_create)

    sql_total_query_order_repeate_sql_create = "select count(stat) total from (SELECT if(casename is null,0,1) stat FROM %s a left join %s b on a.`ordercode`=cast(b.markkey as SIGNED)" % (
    table_name1, table_name2) + " where 1=1" + getsettlecode(dict_param) + \
          getsettleowner(dict_param) + getordercode(dict_param) + getyear(dict_param) + ") tmp where 1=1 " + getstat(dict_param)
    print(sql_total_query_order_repeate_sql_create)

    return sql_query_order_repeate_sql_create, sql_total_query_order_repeate_sql_create



@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def time_list(request):
    # if auth_log().log_check(request) is False:
    #     return JsonResponse({'ret': 302, 'msg': '未登录'}, status=302)
    request.params = json.loads(request.body)
    action = request.params['action']
    if action == "query":
        return time_list_add(request)
    if action == "modify":
        return time_list_modify(request)


def time_list_add(request):
    order_list = request.params.get("data")
    conn = connection
    cursor = conn.cursor()
    for order in order_list:
        start = order['start']
        start = time.strptime(start, "%Y-%m-%d")
        end = order['end']
        end = time.strptime(end, "%Y-%m-%d")
        ordertime = order['ordertime']
        ordertime = time.strptime(ordertime, "%Y-%m-%d")
        if end >= ordertime >= start:
            wrong = 0
        else:
            wrong = 1
        sql = "insert into tp_cheat_record (ordernum,ordertime,start,end,wrong) values ('%s','%s','%s','%s',%s)" % (
            order['ordernum'], order['ordertime'], order['start'], order['end'], wrong)
        cursor.execute(sql)
        conn.commit()
    return JsonResponse("新增成功！", safe=False)


def time_list_modify(request):
    add_list = request.params['add_list']
    conn = connection
    cursor = conn.cursor()
    for order in add_list:
        start = order['start']
        start = time.strptime(start, "%Y-%m-%d")
        end = order['end']
        end = time.strptime(end, "%Y-%m-%d")
        ordertime = order['ordertime']
        ordertime = time.strptime(ordertime, "%Y-%m-%d")
        if end >= ordertime >= start:
            wrong = 0
        else:
            wrong = 1
        sql = "update tp_cheat_record set start=\'%s\',end=\'%s\',wrong=\'%s\' where ordernum=\'%s\'" % (
            order['start'], order['end'], wrong, order['ordernum'])
        cursor.execute(sql)
        conn.commit()
    del_list = request.params['remove_list']
    for order in del_list:
        sql = "delete from tp_cheat_record where ordernum = \'%s\'" % order['ordernum']
        cursor.execute(sql)
        conn.commit()
    time_check()
    return JsonResponse("修改成功！", safe=False)

@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def monitor_list(request):
    # if auth_log().log_check(request) is False:
    #     return JsonResponse({'ret': 302, 'msg': '未登录'}, status=302)
    request.params = json.loads(request.body)
    action = request.params['action']
    if action == "query":
        return show_monitor_list(request)
    if action == "modify":
        return modify_monitor_lsit(request)

@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def statistics_rb(request):
    # if auth_log().log_check(request) is False:
    #     return JsonResponse({'ret': 302, 'msg': '未登录'}, status=302)
    global sql_query_statistics_rb
    request.params = json.loads(request.body)
    type = request.params['type']
    condition = request.params['condition']
    if type == 'total':
        # sql_query_statistics_rb = "select * from(select count(1) as or_num,sum(realrefund) as or_amount from RB_Settle_Check)" \
        #       " a left join (select count(1) as dc_num ,sum(订单产品总价) as dc_amount from datacheat_1 where 存疑类型!='') b on 1=1"
        sql_query_statistics_rb = "select * from(select count(1) as or_num,round(sum(realrefund)/10000,2) as or_amount from RB_Settle_Check)" \
              " a left join (select count(1) as dc_num ,round(sum(订单产品总价)/10000,2) as dc_amount from datacheat_1) b on 1=1"
        print(sql_query_statistics_rb)
    elif type == "year":
        sql_query_statistics_rb = "select if(dc_num is null,0,dc_num) dc_num,if(dc_amount is null,0,dc_amount) dc_amount," \
                                  "if(or_num is null,0,or_num) or_num,if(or_amount is null,0,or_amount) or_amount from(select year(策划案提交时间) as year," \
                                  "count(1) as dc_num ,sum(订单产品总价) as dc_amount from datacheat_1 where 存疑类型!='' and year(`策划案提交时间`) = %s " \
                                  "group by year(策划案提交时间)) a left join (select year(submitdate) as year,count(1) as or_num,sum(realrefund) as or_amount from " \
                                  "RB_Settle_Check where year(submitdate) = %s  group by year(submitdate)) b on a.year=b.year" % (condition, condition)
        print(sql_query_statistics_rb)
    elif type == "season":
        sql_query_statistics_rb = "select if(dc_num is null ,0,dc_num) dc_num,if(dc_amount is null ,0,dc_amount) dc_amount,if(or_num is null ,0,or_num) or_num," \
                                  "if(or_amount is null ,0,or_amount) or_amount from(select count(1) as dc_num ,sum(订单产品总价) as dc_amount from datacheat_1 where " \
                                  "getseason(`策划案提交时间`) = %s and 存疑类型!='' group by getseason(策划案提交时间)) a left join(select count(1) as or_num," \
                                  "sum(realrefund) as or_amount from RB_Settle_Check where getseason(submitdate) = %s group by getseason(submitdate)) b on 1=1" % (condition, condition)
        print(sql_query_statistics_rb)

    dbs = DBUtil()
    res = dbs.get_all(sql_query_statistics_rb)
    if len(res) == 0 or res is None:
        res_list = []
        return JsonResponse({'ret': 0, "data": res_list, 'msg': '查询成功！'}, safe=False)
    else:
        # 订单重复
        res_dict_0 = {"title": "0", "value": res[0][0]}
        # 订单重复金额
        res_dict_1 = {"title": "1", "value": res[0][1]}
        # 数据篡改
        res_dict_2 = {"title": "2", "value": res[0][2]}
        # 数据篡改金额
        res_dict_3 = {"title": "3", "value": res[0][3]}
        res_list = [res_dict_0, res_dict_1, res_dict_2, res_dict_3]
        return JsonResponse({'ret': 0, "data": res_list, 'msg': '查询成功！'}, safe=False)

@api_view(('POST',))
@permission_classes((IsAuthenticated,))
def query_data_cheat_scan_res(request):
    # if auth_log().log_check(request) is False:
    #     return JsonResponse({'ret': 302, 'msg': '未登录'}, status=302)
    request.params = json.loads(request.body)
    data = request.params['data']
    # type = request.params['type']
    dbs = DBUtil_Dict()
    page_size = data.get('page_size')
    page_num = data.get('page_num')
    offset = (page_num-1) * page_size
    data['page_num'] = offset
    sql_query_data_cheat_scan_res, sql_total = query_sql_create('datacheat_1', 'case_relationship', data)
    res = dbs.get_all(sql_query_data_cheat_scan_res)
    res_total = dbs.get_all(sql_total)
    global total_num
    total_num = 0
    if res_total:
        total_num = res_total[0].get("total")
    return JsonResponse({'ret': 0, "total": total_num, "data": res, 'msg': '查询成功！'}, safe=False)


def query_sql_create(table_name1, table_name2, data):
    dict_param = dict()
    for k, v in data.items():
        dict_param[k] = v

    sql_query_order_repeate_sql_create = "select * from (SELECT if(订单SO is null,'',订单SO) ordercode,if(结算案编号 is null,'',结算案编号) settlecode,if(T1代理名称 is null,'',T1代理名称) agent,if(下单时间 is null,'',下单时间) orderdate,'' productmodel,if(订单产品总价 is null,'',订单产品总价) orderproducttotalprice,if(产品数量 is null,'',产品数量) orderproductotaltnum,if(产品单价 is null,'',产品单价) orderproductprice,if(政策解释人 is null,'',政策解释人) settleowner,if(casename is null,0,1) stat FROM %s a left join %s b on a.`订单SO`=cast(b.markkey as SIGNED)" % (
    table_name1, table_name2) + " where 1=1" + getsettlecode_cheat(dict_param) + \
          getsettleowner_cheat(dict_param) + getordercode_cheat(dict_param) + getyear_cheat(dict_param) + ") tmp where 1=1 " + getstat(dict_param) + getpage(dict_param)
    print(sql_query_order_repeate_sql_create)

    sql_total_query_order_repeate_sql_create = "select count(stat) total from (SELECT if(casename is null,0,1) stat FROM %s a left join %s b on a.`订单SO`=cast(b.markkey as SIGNED)" % (
    table_name1, table_name2) + " where 1=1" + getsettlecode_cheat(dict_param) + \
          getsettleowner_cheat(dict_param) + getordercode_cheat(dict_param) + getyear_cheat(dict_param) + ") tmp where 1=1 " + getstat(dict_param)
    print(sql_total_query_order_repeate_sql_create)

    return sql_query_order_repeate_sql_create, sql_total_query_order_repeate_sql_create


def now_process(request):
    return process_show()
